﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Dapper;
using DapperExtensions;
using ExaminationSys.DataAccess.Entity;
using SQLinq;
using SQLinq.Dapper;
using System.IO;
using System.Data.OleDb;

namespace ExaminationSys.DataAccess.Repository
{
    public class DBSubjectRepository : Repository<DBSubject>
    {
        public DBSubjectRepository(SqlConnection context)
            : base(context)
        {
            db = context;
        }
        private SqlConnection db;
        public override DBSubject GetById(dynamic id)
        {
            return base.ObjectSet.Get<DBSubject>(db, id, null, null);
        }
    }

    public class DBSubjectService
    {
        private DBSubjectService()
        {
        }

        public DBSubject Get(Guid id)
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                var result = work.DBSubject.GetById(id);
                return result;

            }
        }

        public bool CheckTitle(string title)
        {
            using (var db = DataContent.DBServer())
            {
                var pg = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
                pg.Predicates.Add(Predicates.Field<DBSubject>(f => f.Title, Operator.Eq, title));
                IUnitOfWork work = new UnitOfWork(db);
                var result = work.DBSubject.GetList(pg).FirstOrDefault();
                return result != null;
            }
        }

        public string GetSerialNo(string strWhere)
        {
            using (var db = DataContent.DBServer())
            {
                var obj = new
                {
                    TableName = "DBSubject",
                    Fields = "SerialNo",
                    OrderField = "SerialNo DESC",
                    sqlWhere = string.Format(" SerialNo like '%{0}%'", strWhere),
                    pageSize = 1,
                    pageIndex = 1,
                };

                var dynamicParam = new DynamicParameters(obj);
                dynamicParam.Add("TotalPage", null, DbType.Int32, ParameterDirection.Output, 32);
                dynamicParam.Add("TotalRecord", 0, DbType.Int32, ParameterDirection.Output, 32);

                var items = db.Query<DBSubject>("PagingRecord", dynamicParam, null, true, null, CommandType.StoredProcedure);
                List<DBSubject> list = items.ToList();
                if (list != null && list.Count > 0)
                {
                    DBSubject model = list[0] as DBSubject;
                    if (model != null)
                    {
                        string temp = model.SerialNo;
                        if (temp != null && temp.Length > 8)
                        {
                            string str1 = model.SerialNo.Substring(0, 8);
                            string str2 = model.SerialNo.Substring(8);
                            int n = int.Parse(str2);
                            if (n < 9999)
                            {
                                string str = (n + 1).ToString();
                                str = SetStringLenght(str, 4);
                                return str1 + str;
                            }
                        }
                    }
                }
                return strWhere + "0001";
            }
        }
        /// <summary>
        /// 指定长度字符串 不足补"0"
        /// </summary>
        /// <param name="orig">目标字符串</param>
        /// <param name="lenght">长度</param>
        /// <returns>指定长字符串</returns>
        private string SetStringLenght(string orig, int lenght)
        {
            string temp = string.Empty;
            if (!string.IsNullOrEmpty(orig))
            {
                byte[] bs = Encoding.Unicode.GetBytes(orig);
                int lx = bs.Length / 2;
                if (lx != lenght)
                {
                    int ly = lenght - lx;
                    if (ly > 0)
                    {
                        for (int i = 0; i < ly; i++)
                        {
                            temp += "0";
                        }
                        temp += Encoding.Unicode.GetString(bs, 0, lx * 2);
                    }
                    else if (ly < 0)
                    {
                        temp = Encoding.Unicode.GetString(bs, 0, lenght * 2);
                    }
                }
                else { temp = orig; }
            }
            else
            {
                for (int i = 0; i < lenght; i++)
                {
                    temp += "0";
                }
            }
            return temp;
        }

        public List<DBSubject> GetAll()
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                var result = work.DBSubject.GetList();
                return result.ToList();
            }
        }

        public bool Delete(List<string> ids)
        {
            if (ids == null || ids.Count == 0) return false;
            bool flag = false;
            using (var db = DataContent.DBServer())
            {
                int retCode = 0;
                foreach (var id in ids)
                {
                    retCode = db.Execute(@"update DBSubject set Status=0 where ID=@TmpId",
                                         new[] { new { TmpId = id } });
                    if (retCode <= 0)
                    {
                        break;
                    }
                }
                flag = true;
            }
            return flag;
        }

        public bool Lock(List<string> ids, int _lock)
        {
            if (ids == null || ids.Count == 0) return false;
            bool flag = false;
            using (var db = DataContent.DBServer())
            {
                int retCode = 0;
                foreach (var id in ids)
                {
                    retCode = db.Execute(@"update DBSubject set Lock=@lk where ID=@TmpId",
                                         new[] { new { lk = _lock, TmpId = id } });
                    if (retCode <= 0)
                    {
                        break;
                    }
                }
                flag = true;
            }
            return flag;
        }

        public bool Update(DBSubject entity)
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                return work.DBSubject.Update(entity);
            }
        }
        public dynamic Insert(DBSubject entity)
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                return work.DBSubject.Insert(entity, null);
            }
        }

        public List<DBSubject> GetListByPage(string sqlwhere, string orderbyFiled, int pagesize, int pageindex, out int totalPage, out int totalRecord)
        {
            if (string.IsNullOrWhiteSpace(orderbyFiled))
                throw new ArgumentNullException("orderbyFiled");

            if (string.IsNullOrWhiteSpace(sqlwhere))
                sqlwhere = " 1=1";
            using (var db = DataContent.DBServer())
            {
                var obj = new
                {
                    TableName = "DBSubject",
                    Fields = "*",
                    OrderField = orderbyFiled,
                    sqlWhere = sqlwhere,
                    pageSize = pagesize,
                    pageIndex = pageindex,
                };

                var dynamicParam = new DynamicParameters(obj);
                dynamicParam.Add("TotalPage", null, DbType.Int32, ParameterDirection.Output, 32);
                dynamicParam.Add("TotalRecord", 0, DbType.Int32, ParameterDirection.Output, 32);

                var items = db.Query<DBSubject>("PagingRecord", dynamicParam, null, true, null, CommandType.StoredProcedure);
                totalPage = dynamicParam.Get<int>("TotalPage");
                totalRecord = dynamicParam.Get<int>("TotalRecord");
                return items.ToList();
            }
        }

        public Int32 LoadColumns(string filepath, string sheetName)
        {
            string strFileName = Path.GetExtension(filepath).ToLower();
            Int32 addcount = 0;

            #region Data

            string strConn;
            if (strFileName == ".xls")
            {

                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath +
                          ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\";";
            }
            else
            {
                strConn =
                    string.Format(
                        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'",
                        filepath);
            }

            #endregion

            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                var ds = new DataSet();
                var oada = new OleDbDataAdapter(string.Format("select * from [{0}]", sheetName), strConn);
                oada.Fill(ds);
                if (ds.Tables.Count > 0)
                {
                    string strWhere = DateTime.Now.ToString("yyyyMMdd");
                    bool sucess = false;
                    List<string> errorList = new List<string>();

                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        if (!Check(row, ref errorList))
                            break;
                        bool exitflag = CheckTitle(row.Field<string>("标题"));

                        if (!exitflag)
                        {
                            DBSubject entity = new DBSubject();
                            string headstr = System.DateTime.Now.ToString("yyyyMMdd");
                            entity.ID = Guid.NewGuid();
                            entity.SerialNo = GetSerialNo(headstr);
                            entity.KeyWord = row.Field<string>("关键字");
                            entity.Knowledge = row.Field<string>("知识点");
                            entity.Stage = row.Field<string>("阶段");
                            entity.SubjectType = row.Field<string>("题型");
                            entity.Challenge = row.Field<string>("难度");
                            entity.Title = row.Field<string>("标题");
                            entity.AttachFile = row.Field<string>("附件");
                            entity.Content = row.Field<string>("内容");

                            entity.Status = 1;
                            entity.AddTime = DateTime.Now;
                            entity.Lock = false;
                            entity.Operator = "admin";
                            entity.OperatorTime = DateTime.Now;

                            var retCode = Insert(entity);
                            sucess = retCode != null;
                            if (sucess)
                                addcount++;
                        }
                    }
                }

            }
            return addcount;
        }

        private bool Check(DataRow row, ref List<string> errorList)
        {
            bool right = true;
            #region 条件
            
            if (string.IsNullOrWhiteSpace(row.Field<string>("标题")))
            {
                errorList.Add("标题字不能为空");
                right = false;
            }
            //if (string.IsNullOrWhiteSpace(row.Field<string>("关键字")))
            //{
            //    errorList.Add("关键字不能为空");
            //    right = false;
            //}
            //if (string.IsNullOrWhiteSpace(row.Field<string>("知识点")))
            //{
            //    errorList.Add("知识点不能为空");
            //    right = false;
            //}
            if (string.IsNullOrEmpty(row.Field<string>("题型")))
            {
                errorList.Add("题型不能为空");
                right = false;
            }
            if (string.IsNullOrEmpty(row.Field<string>("难度")))
            {
                errorList.Add("难度不能为空");
                right = false;
            }
            if (string.IsNullOrEmpty(row.Field<string>("阶段")))
            {
                errorList.Add("阶段不能为空");
                right = false;
            }
            if (string.IsNullOrEmpty(row.Field<string>("内容")))
            {
                errorList.Add("内容不能为空");
                right = false;
            }
            if (!string.IsNullOrEmpty(row.Field<string>("题型"))
                && row.Field<string>("题型").Equals("视听") && string.IsNullOrEmpty(row.Field<string>("附件")))
            {
                errorList.Add("视听题，视听附件不能为空");
                right = false;
            }

            #endregion

            return right;
        }
    }    
}
